package com.jackrain.nea.core.mapper;

import com.alibaba.fastjson.JSONObject;
import com.jackrain.nea.core.entity.AdTableModel;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Component;

import java.sql.Timestamp;

@Mapper
@Component
public interface AdTableMapper {

    @Select("select t.ad_table_id adTableId, t.dbname,g.name from ad_column t,ad_table g where t.ad_table_id=g.id and t.id= #{columnId}")
    AdTableModel select(long columnId);

    @Select("select s.ad_table_id adTableId, t.name from ad_tablesql s, ad_table t where s.id = #{tableSqlId} and t.id = s.ad_table_id")
    AdTableModel selectByTableSqlId(long tableSqlId);

    @Select("select REALTABLE_ID realtableId, name from AD_TABLE where id = #{tableId}")
    AdTableModel selectByTableId(long tableId);

    @Select("select id, pk_column_id pkColumnId ,ak_column_id akColumnId from AD_TABLE where name = #{name}")
    AdTableModel selectByTableName(String name);

    @Select("select count(1) from INFORMATION_SCHEMA.COLUMNS where table_name= #{tableName} and table_schema='ormnds3' and column_name = #{columnName}")
    int columnCount(@Param("tableName") String tableName, @Param("columnName") String columnName);


    @Select("SELECT count(1) from ad_table where ak_column_id = #{akColumnId} or pk_column_id= #{pkColumnId}")
    int count(@Param("akColumnId") long akColumnId, @Param("pkColumnId") long pkColumnId);


    @Update("UPDATE ad_table set modifieddate = #{date} where id = #{tableId}")
    int updateModifieddate(@Param("date") Timestamp date, @Param("tableId") long tableId);

    @Select("select pk_column_id pkColumnId from ad_table where id = #{tableId}")
    String pkColumnId(long tableId);

    @Select("select directory_id from ad_table where id = #{tableId}")
    String directoryId(long tableId);

    @Select("select count(1) from ad_table where directory_id = #{directoryId} and id != #{tableId}")
    int directoryCountNotWithTable(@Param("directoryId") long directoryId, @Param("tableId") long tableId);

    @Update("UPDATE AD_TABLE SET aK_COLUMN_ID = #{columnId} where id = #{tableId}")
    int updateAK(@Param("tableId") long tableId, @Param("columnId") long columnId);

    @Update("update AD_TABLE t SET t.aK_COLUMN_ID = t.PK_COLUMN_ID where t.id= #{id} and not exists (select 1 from ad_column g where g.ad_table_id= #{id} and isak='Y')")
    int updateAK2PK(long id);

    @Update("UPDATE AD_TABLE SET dK_COLUMN_ID = #{columnId} where id = #{tableId}")
    int updateDK(@Param("tableId") long tableId, @Param("columnId") long columnId);

    @Update("update AD_TABLE t SET t.dK_COLUMN_ID = t.aK_COLUMN_ID where t.id= #{id} and not exists (select 1 from ad_column g where g.ad_table_id= #{id} and isdk='Y')")
    int updateDK2PK(long id);


    @Select("select round(IFNULL(max(orderno), 0),-1) + 10  FROM ad_table WHERE ad_tablecategory_id=#{tableCategoryId}")
    int getMaxOrderNoByCategory(long tableCategoryId);

    @Update("update ad_table set orderno = #{orderno} where id = #{id}")
    int updateOrderNo(@Param("id") long id, @Param("orderno") int orderno);

    @Update("update ad_table set DIRECTORY_ID = #{directoryId} where id = #{id}")
    int updateDirectoryId(@Param("id") long id, @Param("directoryId") long directoryId);

    @Update("update ad_table set DIRECTORY_ID = NULL where DIRECTORY_ID = #{directoryId}")
    int updateDirectory2Null(long directoryId);

    @Update("update ad_table set pk_column_id = #{pk}, ak_column_id = #{ak}, dk_column_id = #{dk} where id = #{id}")
    int updatePkAkDk(@Param("id") long id, @Param("pk") long pk, @Param("ak") long ak, @Param("dk") long dk);

    @Update("update ad_table set "
            + "PK_COLUMN_ID =(select id from ad_column where dbname = 'ID' and ad_table_id = #{id}),"
            + "AK_COLUMN_ID =(select id from ad_column where isak = 'Y' and ad_table_id = #{id}),"
            + "DK_COLUMN_ID =(select id from ad_column where isdk = 'Y' and ad_table_id = #{id})"
            + "where id = #{id}")
    int syncPkAkDk(long id);

    @Delete("delete from ad_table where id = #{id}")
    int delete(long id);

    @Select("select name from ad_table where id = #{id}")
    String selectNameById(long id);

    @Select("select * from ad_table where id = #{id}")
    JSONObject selectFullById(long id);

    @Select("select count(1) from ad_table where name = #{tablename} limit 1")
    boolean exists(String tablename);
}
